﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;
namespace DAO
{

    public class LichDiDAO
    {
        public void tangHangHoa(String maKH, String maChuyen, String ngayDi,int khoiLuong)
        {

            SqlTransaction tran;
            ConnectionManager cm = new ConnectionManager();
            SqlConnection conn = cm.getConnection();
            conn.Open();
            SqlCommand command = conn.CreateCommand(); //tao doi tuong command
            tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted, "CapNhatTrongTai");//start tran tren connection nay
            command.Connection = conn;
            command.Transaction = tran;

            command.CommandTimeout = 2000;
            // khach hang ton tai ?
            command.CommandText = "select * from KHACHHANG kh where kh.MAKH=@maKH";
            SqlParameter pa = new SqlParameter("@maKH", SqlDbType.VarChar);
            SqlParameter pa2 = new SqlParameter("@ngayDi", SqlDbType.VarChar);
            SqlParameter pa3 = new SqlParameter("@maChuyen", SqlDbType.VarChar);
            pa.Value = maKH;
            pa2.Value = ngayDi;
            pa3.Value = maChuyen;
            command.Parameters.Add(pa);
            SqlDataReader rd = command.ExecuteReader();
            if (rd.HasRows == false)
            {
                rd.Close();
                tran.Rollback();
                throw new Exception("Loi khong tim thay khach hang");
            }
            command.Parameters.Clear();
            rd.Dispose();
            command.Dispose();
            // khach hang co dat cho ?
            command.CommandText = "select * from KHACHHANG  join DATCHO on KHACHHANG.MAKH=DATCHO.MAKH and KHACHHANG.MAKH=@maKH join LICHDI on LICHDI.NGAYDI=DATCHO.NGAYDI and LICHDI.MACHUYEN=DATCHO.MACHUYEN and LICHDI.MACHUYEN =@maChuyen and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi";
            command.Parameters.Add(pa);
            command.Parameters.Add(pa3);
            command.Parameters.Add(pa2);
            rd = command.ExecuteReader();
            if (rd.HasRows == false)
            {
                rd.Close();
                tran.Rollback();
                throw new Exception("Khách hàng này chưa đăng ký vận chuyển vào chuyến xe này");
            }
            rd.Close();
            command.Dispose();
            command.Parameters.Clear();
            //kiem tra trong tai sau khi cap nhat co am hay khong ?
            command.CommandText = "select LICHDI.TTHIENTAI from KHACHHANG  join DATCHO on KHACHHANG.MAKH=DATCHO.MAKH and KHACHHANG.MAKH=@maKH join LICHDI on LICHDI.NGAYDI=DATCHO.NGAYDI and LICHDI.MACHUYEN=DATCHO.MACHUYEN and LICHDI.MACHUYEN =@maChuyen and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi";
            command.Parameters.Add(pa);
            command.Parameters.Add(pa3);
            command.Parameters.Add(pa2);
            int tt=(int)command.ExecuteScalar();
            if (tt - khoiLuong < 0)
            {
                rd.Close();
                tran.Rollback();
                throw new Exception("Trọng tải xe âm");
            }
            rd.Close();
            command.Dispose();
            command.Parameters.Clear();
            System.Threading.Thread.Sleep(2000);
            tt = tt - khoiLuong;
            command.CommandText = "update LICHDI set TTHIENTAI=@tt where MACHUYEN=@maChuyen and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi";
            command.Parameters.Add(pa);
            command.Parameters.Add(pa3);
            command.Parameters.Add(pa2);
            SqlParameter pa4 = new SqlParameter("@tt", SqlDbType.Int);
            pa4.Value = tt;
            command.Parameters.Add(pa4);
            command.ExecuteNonQuery();

            tran.Commit();
            
            conn.Close();
            conn.Dispose();

        }
        public void test()
        {

            SqlTransaction tran;
            ConnectionManager cm = new ConnectionManager();
            SqlConnection conn = cm.getConnection();
            conn.Open();
            SqlCommand command = conn.CreateCommand(); //tao doi tuong command
            tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted, "CapNhatTrongTai");//start tran tren connection nay
            
            command.Connection = conn;
            command.Transaction = tran;
            command.CommandTimeout = 2000;
            // khach hang ton tai ?
            command.CommandText = "update KHACHHANG set TEN='111111111111' where MAKH='KH00000001'";
            command.ExecuteNonQuery();
            tran.Rollback();    
            conn.Close();
            conn.Dispose();

        }
    }
}
